package com.hnkywl.sts.dao.cwgl;

import com.hnkywl.sts.dao.sys.YgDao;
import com.hnkywl.sts.dao.xsgl.KhDao;
import com.hnkywl.sts.entity.cggl.Cgd;
import com.hnkywl.sts.entity.cggl.Cgdmx;
import com.hnkywl.sts.entity.organ.Ck;
import com.hnkywl.sts.entity.organ.Nbgs;
import com.hnkywl.sts.entity.xsgl.Xsd;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-7-7
 * Time: 上午11:12
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class CgrkDao extends SimpleHibernateTemplate<Cgd, Long>  {
    private YgDao ygDao;

    public Page<Cgd> findAllCgdList(Integer sfgq, String wzbm, String cgdh, Long dzckId, Date fhrqStart, Date fhrqEnd, String cglx, Long cggsId, Long cgbmId, Long cgrId, String zt, String sftzd, String htType, Long gysid, String rkzt, String zdr, Boolean dlkhcgd, int pageNo, int pageSize) throws ParseException {
        Page<Cgd> page = new Page<Cgd>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("id");

        DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Cgdmx.class);

        detachedCriteria.createAlias("gg", "gg", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("cgd.id").as("cgd.id")))
                .add(Restrictions.or(Restrictions.like("gg.code", wzbm, MatchMode.START), Restrictions.like("gg.oldcode", wzbm, MatchMode.START)));

        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("gys", "gys", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbbm", "sbbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cggs", "cggs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgbm", "cgbm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cgr", "cgr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("dzck", "dzck", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                                .add(Property.forName("cgsl").as("cgsl"))
                                .add(Property.forName("cgje").as("cgje"))
                                .add(Property.forName("cgbhsje").as("cgbhsje"))
                                .add(Property.forName("glfyje").as("glfyje"))
                                .add(Property.forName("id").as("id"))
                                .add(Property.forName("ysh").as("ysh"))
                                .add(Property.forName("gys.id").as("gys_id"))
                                .add(Property.forName("gys.qc").as("gys_qc"))
                                .add(Property.forName("gys.jc").as("gys_jc"))
                                .add(Projections.sqlProjection("this_.cgdh as cgdh", new String[]{"cgdh"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.cglx as cglx", new String[]{"cglx"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.fhrq as fhrq", new String[]{"fhrq"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.sqlProjection("this_.jhdhrq as jhdhrq", new String[]{"jhdhrq"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.sqlProjection("this_.cgsj as cgsj", new String[]{"cgsj"}, new Type[]{Hibernate.DATE}))
                                .add(Projections.sqlProjection("this_.yrk as yrk", new String[]{"yrk"}, new Type[]{Hibernate.STRING}))
                                .add(Property.forName("sbgs.id").as("sbgs_id"))
                                .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                                .add(Property.forName("sbbm.id").as("sbbm_id"))
                                .add(Property.forName("sbbm.qc").as("sbbm_qc"))
                                .add(Property.forName("sbr.id").as("sbr_id"))
                                .add(Property.forName("sbr.xm").as("sbr_xm"))
                                .add(Property.forName("cggs.id").as("cggs_id"))
                                .add(Property.forName("cggs.qc").as("cggs_qc"))
                                .add(Property.forName("cggs.jc").as("cggs_jc"))
                                .add(Property.forName("cgbm.id").as("cgbm_id"))
                                .add(Property.forName("cgbm.qc").as("cgbm_qc"))
                                .add(Property.forName("cgr.id").as("cgr_id"))
                                .add(Property.forName("cgr.xm").as("cgr_xm"))
                                .add(Property.forName("dzck.id").as("dzck_id"))
                                .add(Property.forName("dzck.mc").as("dzck_mc"))
                                .add(Property.forName("insertFlag").as("insertFlag"))
                                .add(Property.forName("deleteFlag").as("deleteFlag"))
                                .add(Property.forName("createUser.xm").as("createUser_xm"))
                                .add(Projections.sqlProjection("this_.sftzd as sftzd", new String[]{"sftzd"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                                .add(Projections.sqlProjection("this_.date_created as cjsj", new String[]{"cjsj"}, new Type[]{Hibernate.DATE}))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(wzbm)) {
            criteria.add(Property.forName("id").in(detachedCriteria));
        }
        if (StringUtils.isNotEmpty(cgdh)) {
            criteria.add(Property.forName("cgdh").like(cgdh, MatchMode.ANYWHERE));
        }
        if (StringUtils.isNotEmpty(zdr)) {
            criteria.add(Property.forName("createUser.xm").like(zdr, MatchMode.ANYWHERE));
        }
        if (fhrqStart != null) {
            criteria.add(Restrictions.ge("dateCreated", sDaysAfterNowDate(fhrqStart)));
        } else {
            Date start = sDaysAfterNowDate(new Date());
            criteria.add(Restrictions.ge("dateCreated", start));
        }
        if (fhrqEnd != null) {
            criteria.add(Restrictions.le("dateCreated", jDaysAfterNowDate(fhrqEnd)));
        } else {
            Date end = jDaysAfterNowDate(new Date());
            criteria.add(Restrictions.le("dateCreated", end));
        }
        if (sfgq != null && sfgq != 0) {
            Date end = DaysAfterNowDate(new Date());
            if (sfgq == 1) {
                criteria.add(Restrictions.ge("jhdhrq", end));
            } else {
                criteria.add(Restrictions.lt("jhdhrq", end));
            }
        }
        if (StringUtils.isNotEmpty(zt)) {
            if (zt != null && !zt.isEmpty() && "ybc".equals(zt)) {
                criteria.add(Restrictions.eq("zt", zt));
            } else if (zt != null && !zt.isEmpty() && "ytj".equals(zt)) {
                criteria.add(Restrictions.eq("zt", zt));
            } else if (zt != null && !zt.isEmpty() && "ysp".equals(zt)) {
                criteria.add(Restrictions.eq("zt", zt));
            }
        }

        if (StringUtils.isNotEmpty(htType) && htType.equals("bght")) {
            criteria.add(Restrictions.eq("cglx", htType));
        } else {
            criteria.add(Restrictions.ne("cglx", "bght"));
        }

        if (StringUtils.isNotEmpty(cglx)) {
            criteria.add(Restrictions.eq("cglx", cglx));
        }
        if (dzckId != null) {
            criteria.add(Restrictions.eq("dzck.id", dzckId));
        }
        if (cggsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", cggsId));
        }
        if (cgbmId != null) {
            criteria.add(Restrictions.eq("sbbm.id", cgbmId));
        }
        if (cgrId != null) {
            criteria.add(Restrictions.eq("sbr.id", cgrId));
        }
        if (gysid != null) {
            criteria.add(Restrictions.eq("gys.id", gysid));
        }
        if (StringUtils.isNotEmpty(rkzt)) {
            criteria.add(Restrictions.eq("yrk", rkzt.equals("true")));
        }

        if (sftzd != null) {
            criteria.add(Restrictions.or(Restrictions.isNull("sftzd"), Restrictions.eq("sftzd", false)));
        } else {
            criteria.add(Restrictions.eq("zt", "ysp"));
            criteria.add(Restrictions.or(Restrictions.isNull("yjs"), Restrictions.eq("yjs", false)));
        }

        if (dlkhcgd != null && dlkhcgd) {
            DetachedCriteria subDetachedCriteria = DetachedCriteria.forClass(Nbgs.class);
            subDetachedCriteria
                    .setProjection(Projections.projectionList()
                            .add(Property.forName("id").as("id")));
            criteria.add(Property.forName("sbgs.id").in(subDetachedCriteria));
        }

        createCgdDataRegion(criteria);
        criteria.add(Restrictions.ne("cglx", "nbcg"));
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    private void createCgdDataRegion(Criteria criteria) {
        criteria.createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN);
        List<Ck> list = this.ygDao.get(YgService.getCurrentYg().getId()).getCkList();
        if (list != null && list.size() > 0) {
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    break;
                case 1:
                    criteria.add(Restrictions.in("dzck", list));
                    break;
                case 2:
                    criteria.add(Restrictions.in("dzck", list));
                    break;
                case 3:
                    criteria.add(Restrictions.in("dzck", list));
                    break;
            }
        } else {
            SimpleExpression se = null;
            switch (YgService.getCurrentYgCxfw()) {
                case 0:
                    return;
                case 1:
                    se = Restrictions.eq("createOrgan.id", YgService.getCurrentYgZgGsId());
                    break;
                case 2:
                    se = Restrictions.eq("createDepartment.id", YgService.getCurrentYgGsId());
                    break;
                case 3:
                    se = Restrictions.eq("createUser.id", YgService.getCurrentYg().getId());
                    break;
            }
            criteria.add(
                    Restrictions.or(se,
                            Restrictions.eq("sbgs.id", YgService.getCurrentYgZgGsId())
                    )
            );
        }
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date jDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    //   当前日期加减n天后的日期，返回String   (yyyy-mm-dd)
    public Date sDaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 0);
        rightNow.set(java.util.Calendar.MINUTE, 0);
        rightNow.set(java.util.Calendar.SECOND, 0);
        return rightNow.getTime();
    }

    public Date DaysAfterNowDate(Date date) {
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(date);
        rightNow.add(Calendar.DAY_OF_MONTH, -1);
        rightNow.set(java.util.Calendar.HOUR_OF_DAY, 23);
        rightNow.set(java.util.Calendar.MINUTE, 59);
        rightNow.set(java.util.Calendar.SECOND, 59);
        return rightNow.getTime();
    }

    public List<Map> getVoucher(Cgd cgd) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = getSession().connection();
            call = con.prepareCall("{Call VOUCHER_CGRK(?,?)}");
            call.setLong(1, cgd.getId());
            call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
            call.execute();
            //====================================
            rs = ((OracleCallableStatement) call).getCursor(2);
            ResultSetMetaData resultsMetaData = rs.getMetaData();
            int columnCount = resultsMetaData.getColumnCount();
            List<Map> result = new ArrayList<Map>();
            while (rs.next()) {
                Map<String, Object> r = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = resultsMetaData.getColumnName(i).toLowerCase();
                    r.put(columnName, rs.getObject(i));
                }
                result.add(r);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        } finally {
            closeResource(rs, call, con);
        }
    }

    private void closeResource(ResultSet rs, Statement s, Connection con) {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (s != null)
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
        if (con != null)
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            }
    }



    @Autowired
    public void setYgDao(YgDao ygDao) {
        this.ygDao = ygDao;
    }
}
